---
title: Get Oracle connection string
summary: Returns a validated, cached Oracle connection string composed from an Environment API template and a secure credential.
status: draft
owner: Architecture
last_reviewed: 2026-10-09
operation_id: OP-03
trigger: GET /v1/Connections/ORA/{instance}/{username}
---

# OP-03: Get Oracle connection string

This operation composes an Oracle connection string for the requested instance (a TNS alias) and user, proves that it connects by running a constant probe query, caches it for the lifetime of the process, and returns it to the caller. It follows the same flow as the SQL Server operation, with an Oracle key format, template and probe statement.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-03 |
| **Trigger** | HTTP request from an authorized caller |
| **Entry endpoint** | [`GET /v1/Connections/ORA/{instance}/{username}`](../endpoints/connections.md#get-v1-connections-ora-instance-username) |
| **Owning component** | `ConnectionsController.GetOraConnectionString`, delegating to `ConnectionManager.GetOracleConnectionString` |
| **Inputs** | Route values `instance` and `username` (inferred route binding); Environment API template `oraconnectiontemplate` and a secure credential for the composed key |
| **Outputs** | `ActionResult<string>` holding the credential-bearing connection string |
| **Failure response** | 400 for an unresolved TNS alias, 404 for a missing secure setting, 500 for other probe or credential failures, produced from `ConnectionManagerException` by package middleware† |

## Steps

1. Authorize the caller against resource `DBConnAPIRetrieveResource` ([BR-DBC-025](../business-rules.md#br-dbc-025-resource-authorization-on-every-endpoint))
   - On failure: a denial produced by the package authorization handler†; the exact status is not visible in source**
2. Write the audit log entry with the key parts, `UserId`, `AuthenticationScheme` and the time ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged))
3. Build the cache key `ORA.<instance>.<user>`, which is case-sensitive ([BR-DBC-002](../business-rules.md#br-dbc-002-cache-key-format))
4. Build the lower-cased secure-setting key `ora_<instance>_<user>` ([BR-DBC-005](../business-rules.md#br-dbc-005-secure-setting-keys-are-lower-cased))
5. Read the template `oraconnectiontemplate` on every request, before the cache check, and replace `{INSTANCE}` only ([BR-DBC-007](../business-rules.md#br-dbc-007-templates-come-from-the-environment-api-group))
6. Look the key up in the in-memory cache ([BR-DBC-001](../business-rules.md#br-dbc-001-serve-from-the-cache-when-the-key-is-present))
   - On a hit: return the cached string with status 200 and stop. Only the secure-credential retrieval, validation, the probe and cache insertion are skipped; the template lookup in step 5 already happened
7. On a miss, check that the secure setting exists ([BR-DBC-009](../business-rules.md#br-dbc-009-missing-secure-setting-returns-404))
   - On failure: `ConnectionManagerException` with status 404
8. Verify the backoff configuration ([BR-DBC-011](../business-rules.md#br-dbc-011-backoff-list-must-cover-the-retry-attempts))
9. Decrypt the secure setting and split it at the first pipe ([BR-DBC-010](../business-rules.md#br-dbc-010-credential-split-at-the-first-pipe%2C-unguarded))
   - A missing pipe raises `ArgumentOutOfRangeException` outside the retry loop; the status is not visible in source**
10. Substitute `{USER}` and `{PW}` ([BR-DBC-008](../business-rules.md#br-dbc-008-credentials-are-substituted-into-the-template))
11. Probe the composed string, up to four attempts with 2 s, 3 s and 5 s waits ([BR-DBC-012](../business-rules.md#br-dbc-012-four-probe-attempts-with-2%2C-3-and-5-second-backoff))
    - Each attempt decodes the legacy token for the string under test only ([BR-DBC-019](../business-rules.md#br-dbc-019-legacy-token-decoded-for-the-probe-only))
    - The probe opens a unit of work through `IUnitOfWorkFactory` and runs `SELECT 1 FROM DUAL`; a non-null result means valid ([BR-DBC-018](../business-rules.md#br-dbc-018-probe-validity-definition))
    - A false result counts as a failure with status 500 ([BR-DBC-014](../business-rules.md#br-dbc-014-a-false-probe-result-is-a-failure))
    - Permanent errors (`ORA-12154`, `ORA-12504`, `TNS-12154`, `ORA-01017`, `ORA-01000`) stop the loop at once ([BR-DBC-013](../business-rules.md#br-dbc-013-permanent-errors-stop-retrying))
12. After the final failure, map the error to a status
    - TNS alias errors map to 400 ([BR-DBC-015](../business-rules.md#br-dbc-015-tns-alias-errors-map-to-400))
    - `ORA-01034`, `ORA-01000`, `ORA-01017`, `ORA-12570`, `ORA-12571`, `ORA-03135` and the generic fallback map to 500 ([BR-DBC-016](../business-rules.md#br-dbc-016-other-final-failures-map-to-500))
    - An existing `ConnectionManagerException` is rethrown with its status ([BR-DBC-017](../business-rules.md#br-dbc-017-an-existing-typed-exception-keeps-its-status))
13. On success, log the validation and add the string to the cache under the lock ([BR-DBC-003](../business-rules.md#br-dbc-003-guarded-cache-insert%2C-first-writer-wins), [BR-DBC-004](../business-rules.md#br-dbc-004-cache-entries-never-expire))
14. Return the connection string with status 200

> **Origin**: Steps 1 and 12 depend on `SAIF.NetCore.ServiceExtensions`† (authorization handler and `DynamicErrorHandler`), and step 11 uses `IUnitOfWorkFactory` from `SAIF.NetCore.NHibernate`†, both retrieved via the Legends CLI RAG service and executed within this operation.

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
    A["Authorize DBConnAPIRetrieveResource"] -->|"Denied"| Z["Authorization denial, status not visible in source"]
    A --> B["Audit log, build cache key and secure-setting key"]
    B --> T["Read Environment API template (every request)"]
    T -->|"Template missing"| X["Unhandled/package error, status not visible in source"]
    T --> C{"Key in cache?"}
    C -->|"Yes"| R["Return cached string, 200"]
    C -->|"No"| D{"Secure setting exists?"}
    D -->|"No"| E["404 ConnectionManagerException"]
    D -->|"Yes"| F["Decrypt credential, split at first pipe"]
    F -->|"No pipe"| X2["Unhandled/package error, status not visible in source"]
    F --> G["Compose string"]
    G --> H["Probe SELECT 1 FROM DUAL, up to 4 attempts"]
    H -->|"Valid"| I["Add to cache and return 200"]
    H -->|"TNS alias error"| J["400"]
    H -->|"Other failure"| K["500"]
```

## Transaction and side effects

| Aspect | Finding |
| --- | --- |
| Unit of work | `IUnitOfWorkFactory` (`SAIF.NetCore.NHibernate`), a throw-away unit of work per probe attempt; no explicit transaction |
| Commit point | None |
| Records written | None; the probe selects a constant from the Oracle `DUAL` pseudo-table ([data model](../data-model.md)) |
| Downstream calls | SAIF Environment API through `IEnvironmentClient`; the caller-named Oracle alias through the probe |
| Repeat-call behavior | A repeat call for an identical key reads the template again and is then served from the cache, skipping the secure read and probe; the cache is never invalidated ([BR-DBC-004](../business-rules.md#br-dbc-004-cache-entries-never-expire)) |

## Logging

| Surface | Written content |
| --- | --- |
| `ISaifLogService` (Info) | Audit entry per request ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged)); a validation success entry with the attempt count |
| `ISaifLogService` (Error) | Each failed probe attempt with the exception and message |

## Error handling

- TNS alias not found: 400 from `ConnectionManagerException` through package middleware (`DynamicErrorHandler`†)
- Secure setting missing: 404 from `ConnectionManagerException` through the same middleware
- Database unavailable, cursor exhaustion, invalid credentials, network interruption, probe returned false or any other failure: 500 through the same middleware; messages can include the key name, the track and raw provider text
- Missing pipe or missing template: unhandled exceptions; the status is not visible in source**
- Authorization failure: produced by the package authorization handler†; the status is not visible in source**

## Related documentation
- [API index](../index.md)
- [Overview](../overview.md)
- [Architecture](../architecture.md)
- [Hosting pipeline](../hosting-pipeline.md)
- [Endpoints](../endpoints.md)
- [Authentication and authorization](../authentication-authorization.md)
- [Dependencies](../dependencies.md)
- [Configuration](../configuration.md)
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [Data model](../data-model.md)
- [Operational runbook](../operational-runbook.md)
- [Tech debt](../tech-debt.md)

> **Note:** Statements marked with `†` are derived from dependency library
> documentation via the Legends CLI. Statements marked with `**` could
> not be confirmed; treat as unknown until verified.

## Sources
- DBConnectionAPI/DBConnectionAPI/Controllers/ConnectionsController.cs
- DBConnectionAPI/DBConnectionAPI/Services/ConnectionManager.cs
- DBConnectionAPI/DBConnectionAPI/Services/IConnectionManager.cs
- DBConnectionAPI/DBConnectionAPI/Exceptions/ConnectionManagerException.cs
- DBConnectionAPI/DBConnectionAPI/EnvironmentKeys.cs